﻿using JNPF.Utils;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;

namespace JNPF.Data.Util
{
    /// <summary>
    /// 数据库访问扩展
    /// 版 本：V3.0.0
    /// 版 权：引迈信息技术有限公司（https://www.jnpfsoft.com）
    /// 作 者：JNPF开发平台组
    /// </summary>
    public static class DataHelper
    {
        private static string dataBaseType = Appsettings.app(new string[] { "JNPF_DB_Default", "ConnectionType" });

        /// <summary>
        /// 转换连接字符串
        /// </summary>
        /// <param name="dbType">数据驱动</param>
        /// <param name="host">主机</param>
        /// <param name="port">端口</param>
        /// <param name="userName">账户</param>
        /// <param name="password">密码</param>
        /// <param name="database">数据库</param>
        /// <returns></returns>
        public static string ToConnectionString(DatabaseType dbType, string host, int? port, string userName, string password, string database)
        {
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    return string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", host, database, userName, password);
                case DatabaseType.Oracle:
                    return string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4}", host, port, database, userName, password);
                case DatabaseType.MySql:
                    return string.Format("server={0};port={1};database={2};user={3};password={4}", host, port, database, userName, password);
                default:
                    throw new Exception("数据库类型目前不支持");
            }
        }

        /// <summary>
        /// 根据数据库类型获取不同数据库连接对象
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="connectionString"></param>
        /// <param name="con"></param>
        public static DbConnection GetConnection(DatabaseType dbType, string connectionString)
        {
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    return new SqlConnection(connectionString);
                case DatabaseType.MySql:
                    return new MySqlConnection(connectionString);
                case DatabaseType.Oracle:
                    return new OracleConnection(connectionString);
                default:
                    throw new Exception(dbType + "不支持");
            }
        }


        /// <summary>
        /// 根据连接字符串的providerName 转换数据库类型
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public static DatabaseType ToDatabaseType(this string providerName)
        {
            switch (providerName)
            {
                case "SqlServer":
                    return DatabaseType.SqlServer;
                case "System.Data.SqlClient":
                    return DatabaseType.SqlServer;
                case "System.Data.SqlClient.SqlConnection":
                    return DatabaseType.SqlServer;
                case "Microsoft.Data.SqlClient.SqlConnection":
                    return DatabaseType.SqlServer;
                case "Oracle":
                    return DatabaseType.Oracle;
                case "MySql":
                    return DatabaseType.MySql;
                case "MySql.Data.MySqlClient.MySqlConnection":
                    return DatabaseType.MySql;
                default:
                    throw new Exception(providerName + "不支持");
            }
        }
        /// <summary>
        /// CreateParameter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <param name="direction"></param>
        /// <returns></returns>
        public static DbParameter CreateParameter(string name, object value, DbType? dbType = null, ParameterDirection? direction = null)
        {
            DbParameter parameter = null;
            switch (dataBaseType)
            {
                case "SqlServer":
                    parameter = new SqlParameter();
                    break;
                case "MySql":
                    parameter = new MySqlParameter();
                    break;
                case "Oracle":
                    parameter = new OracleParameter();
                    break;
                default:
                    break;
            }
            parameter.ParameterName = name;
            parameter.Value = value;
            if (dbType != null)
                parameter.DbType = (DbType)dbType;
            if (direction != null)
                parameter.Direction = (ParameterDirection)direction;
            return parameter;
        }
        /// <summary>
        /// 执行查询 返回IDataReader
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(this DbConnection cnn, string sql, DbParameter[] param = null, DbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var cmd = cnn.CreateCommand();
            PrepareCommand(cnn, cmd, transaction, commandTimeout, commandType, sql, param);
            var reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return reader;
        }
        /// <summary>
        /// 执行查询 返回IDataReader
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<IDataReader> ExecuteReaderAsync(this DbConnection cnn, string sql, DbParameter[] param = null, DbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var cmd = cnn.CreateCommand();
            PrepareCommand(cnn, cmd, transaction, commandTimeout, commandType, sql, param);
            var reader = await cmd.ExecuteReaderAsync();
            cmd.Parameters.Clear();
            return reader;
        }
        /// <summary>
        /// 执行查询，返回结果集
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name=""></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static object ExecuteScalar(this DbConnection cnn, string sql, DbParameter[] param = null, DbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var cmd = cnn.CreateCommand();
            PrepareCommand(cnn, cmd, transaction, commandTimeout, commandType, sql, param);
            object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return obj;
        }
        /// <summary>
        /// 执行查询，返回结果集
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name=""></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<object> ExecuteScalarAsync(this DbConnection cnn, string sql, DbParameter[] param = null, DbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var cmd = cnn.CreateCommand();
            PrepareCommand(cnn, cmd, transaction, commandTimeout, commandType, sql, param);
            object obj = await cmd.ExecuteScalarAsync();
            cmd.Parameters.Clear();
            return obj;
        }
        /// <summary>
        /// 为即将执行准备一个命令
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmd"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        private static void PrepareCommand(DbConnection conn, DbCommand cmd, DbTransaction transaction, int? commandTimeout, CommandType? commandType, string sql, DbParameter[] param = null)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.CommandType = commandType == null ? CommandType.Text : (CommandType)commandType;
                if (commandTimeout != null)
                    cmd.CommandTimeout = (int)commandTimeout;
                if (transaction != null)
                    cmd.Transaction = transaction;
                if (param != null)
                    cmd.Parameters.AddRange(param);
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #region MySql
        /// <summary>
        /// mysql查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mySqlConnection"></param>
        /// <returns></returns>
        public static async Task<DataTable> GetMySqlResult(string sql, MySqlConnection mySqlConnection, params MySqlParameter[] parameters)
        {
            //存放结果集
            try
            {
                DataSet dataset = new DataSet();
                mySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                await adapter.FillAsync(dataset);
                cmd.Dispose();
                mySqlConnection.Close();
                var dt = dataset.Tables[0];
                return dt;
            }
            catch (Exception e)
            {
                LogFactory.GetLogger().Error(e);
                throw;
            }
        }

        /// <summary>
        /// mysql查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mySqlConnection"></param>
        /// <returns></returns>
        public static DataTable GetMySqlResultByNoAsync(string sql, MySqlConnection mySqlConnection, params MySqlParameter[] parameters)
        {
            //存放结果集
            try
            {
                DataSet dataset = new DataSet();
                mySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(dataset);
                cmd.Dispose();
                mySqlConnection.Close();
                var dt = dataset.Tables[0];
                return dt;
            }
            catch (Exception e)
            {
                LogFactory.GetLogger().Error(e);
                throw;
            }
        }

        /// <summary>
        /// mysql增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mySqlConnection"></param>
        /// <returns></returns>
        public static async Task<int> ExecuteMySql(string sql, MySqlConnection mySqlConnection, params MySqlParameter[] parameters)
        {
            //存放结果集
            try
            {
                DataSet dataset = new DataSet();
                mySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                int result = await cmd.ExecuteNonQueryAsync();
                cmd.Dispose();
                mySqlConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                LogFactory.GetLogger().Error(e);
                throw;
            }

        }

        /// <summary>
        /// 执行分页mysql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mySqlConnection"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public static async Task<PageResult> GetMySqlPageResult(string sql, MySqlConnection mySqlConnection, PageInput input)
        {
            try
            {
                PageResult pageResult = new PageResult();
                //总条数
                var dt = await GetMySqlResult(sql, mySqlConnection);
                pageResult.pagination = new PageOutput()
                {
                    pageSize = input.pageSize,
                    currentPage = input.currentPage,
                    total = dt.Rows.Count
                };
                //执行分页sql
                var dbSql = new StringBuilder(sql);
                dbSql.Append(string.Format("  limit {0},{1}", (input.currentPage - 1) * input.pageSize, input.pageSize));
                var resultDt = await GetMySqlResult(dbSql.ToString(), mySqlConnection);
                for (int i = 0; i < resultDt.Columns.Count; i++)
                {
                    resultDt.Columns[i].ColumnName = resultDt.Columns[i].ColumnName.ToLower();
                }
                pageResult.list = resultDt;
                return pageResult;
            }
            catch (Exception ex)
            {
                LogFactory.GetLogger().Error(ex);
                throw;
            }
        }
        #endregion
    }
}
